package com.gljx.web.controller;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.ws.RequestWrapper;

import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gljx.web.service.ChargeClazzPremiumService;
import com.gljx.web.service.ClazzService;
import com.gljx.web.service.CustomLogService;
import com.gljx.web.service.DepartmentService;

@Controller
@RequestMapping("/")
public class ChargeClazzPremiumController {

	//自动注入
	@Autowired
	private ChargeClazzPremiumService chargeClazzPremiumService;
	
	//自动注入
	@Autowired
	private DepartmentService departmentService;

	//自动注入
	@Autowired
	private CustomLogService customLogService;
			
	//自动注入
	@Autowired
	private StudentController studentController;
	
	//自动注入
	@Autowired
	private ClazzService clazzService;
	
	/**
	 * 查询转班退费单
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryChargeClazzPremium/look.action")
	public String queryChargeClazzPremium(HttpServletRequest request,String type){
		PageBean<ChargeClazzPremium> pb = new PageBean<>();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//时间格式化
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//获取当前时间
		String endTime = sdf.format(new Date());
		//获取前三天
		String beginTime = TimeFormat.getThreeDay();
		try {
			//获取分页
			pb = getPageBean(request,user.getFcompanyid(),beginTime,endTime);
			//获取分页数据
			List<ChargeClazzPremium> chargeClazzPremiums = pb.getBeanList();
			for (ChargeClazzPremium chargeClazzPremium : chargeClazzPremiums) {
				//获取子类信息
				List<ChargeClazzPremiumEntry>  chargeClazzPremiumEntrys = chargeClazzPremiumService.selectChargeClazzPremiumEntry(chargeClazzPremium.getFid(), "", "", "");
				//放入集合
				chargeClazzPremium.setChargeClazzPremiumEntrys(chargeClazzPremiumEntrys);
			}
			//合计信息总条数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//把数据传到页面
			request.setAttribute("messageAmount",messageAmount);
			request.setAttribute("chargeClazzPremiums", chargeClazzPremiums);
			request.setAttribute("pb", pb);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("flag", type);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/financialManagement/chargeClazzPremiums/chargeClazzPremiumsList";
	}
	
	/**
	 * 根据条件查询
	 * @param request
	 * @param studentName 学员姓名
	 * @param cfidcardno 身份证号
	 * @param cfmobile 手机号
	 * @param beginTime 业务开始时间
	 * @param endTime 业务结束时间
	 * @return
	 */
	@RequestMapping("/queryChargeClazzPremiumByInfo")
	public String queryChargeClazzPremiumByInfo(HttpServletRequest request,String studentName,String cfidcardno,String cfmobile,String beginTime,String endTime){
		PageBean<ChargeClazzPremium> pb = new PageBean<>();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		try {
			//去除空格
			studentName = studentName.trim();
			cfidcardno = cfidcardno.trim();
			cfmobile = cfmobile.trim();
			//获取分页
			pb = getPageBean2(request,user.getFcompanyid(),studentName,cfidcardno,cfmobile,beginTime,endTime);
			//获取分页数据
			List<ChargeClazzPremium> chargeClazzPremiums = pb.getBeanList();
			for (ChargeClazzPremium chargeClazzPremium : chargeClazzPremiums) {
				//获取子类信息
				List<ChargeClazzPremiumEntry>  chargeClazzPremiumEntrys = chargeClazzPremiumService.selectChargeClazzPremiumEntry(chargeClazzPremium.getFid(), studentName, cfidcardno, cfmobile);
				//放入集合
				chargeClazzPremium.setChargeClazzPremiumEntrys(chargeClazzPremiumEntrys);
			}
			//合计信息总条数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//把数据传到页面
			request.setAttribute("messageAmount",messageAmount);
			request.setAttribute("chargeClazzPremiums", chargeClazzPremiums);
			request.setAttribute("pb", pb);
			request.setAttribute("studentName", studentName);
			request.setAttribute("cfidcardno", cfidcardno);
			request.setAttribute("cfmobile", cfmobile);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/financialManagement/chargeClazzPremiums/chargeClazzPremiumsList";
	}
	
	/**
	 * 跳转到添加页面
	 * @param request
	 * @param type
	 * @return
	 */
	@RequestMapping("/insertChargeClazzPremium")
	public String insertChargeClazzPremium(HttpServletRequest request,String type){
		//查询班级
		List<Clazz> clazzs = clazzService.selectClazzAll();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//获取部门
		Department department = departmentService.queryAdminByFid(user.getFcompanyid());
		//获取最大编码
        /*String fnumber = customLogService.selectFunmber("ct_ds_transferclazzrefundbill",user.getFcompanyid());*/
		//获取下一个编码
        String number = GetFnumber.fnumbers("QTTFD");
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//开始时间
		String endTime = sdf.format(new Date());
		//开结束时间
		String beginTime = TimeFormat.getMonth();
		try {
            Map map = studentController.getPageAsTrainCharge(request, "", "","0", "","","", beginTime, endTime);//调用学员方法
			request.setAttribute("flag", 1);				//是否为新增记录	
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("pb", map.get("pb"));
			request.setAttribute("students", map.get("sList"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		//把数据传到页面
		request.setAttribute("clazzs", clazzs);
		request.setAttribute("type", type);
		request.setAttribute("name", user.getName());
		request.setAttribute("department", department);
		request.setAttribute("fnumber", number);
		return "/backHome/financialManagement/chargeClazzPremiums/editChargeClazzPremiums";
	}

	/**
	 * 增加过滤功能
	 * @param studentName
	 * @param studentCard
	 * @param request
	 * @return
	 */
	@RequestMapping("/selectByClassPremium")
	@ResponseBody
	public String selectByClassPremium(String studentName,String studentCard, HttpServletRequest request){
		ChargeClazzPremiumEntry chargeClazzPremiumEntry=new ChargeClazzPremiumEntry();
		User user=(User)request.getSession().getAttribute("user");
		List<ChargeClazzPremiumEntry> chargeClazzPremiumEntryList=chargeClazzPremiumService.selectChargeClazzPremiumByInfo(user.getFcompanyid(),studentName,studentCard,"","","");
       if(chargeClazzPremiumEntryList.size()!=0&&!studentName.equals("")){

         return "0";
       }
		return "1";
	}

	/**
	 * 添加或修改
	 * @param request
	 * @param fid 用于判断添加修改
	 * @param chargeClazzPremium
	 * @return
	 */
	@RequestMapping("/editChargeClazzPremiums")
	public String editChargeClazzPremiums(HttpServletRequest request,String fid,ChargeClazzPremium chargeClazzPremium){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//控制单元id
		chargeClazzPremium.setFcontrolunitid(user.getFcompanyid());
		//公司id
		chargeClazzPremium.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String time = TimeFormat.timeFormat(new Date());
		//操作人id
		chargeClazzPremium.setFhandlerid(user.getFid());
		//**获取子类信息集合**//*
		//序列
		String[] fseq = request.getParameterValues("fseq");
		//学员编码
		String[] cfstudentno = request.getParameterValues("cfstudentno");
		//学员id
		String[] cfstudentid = request.getParameterValues("cfstudentid");
		//身份证号
		String[] cfidcardno = request.getParameterValues("cfidcardno");
		//性别
		String[] cfsex = request.getParameterValues("cfsex");
		//手机
		String[] cfmobile = request.getParameterValues("cfmobile");
		//学员名称
        String[] studentName = request.getParameterValues("studentName");
        //当前班级
		//String[] dqClazz = request.getParameterValues("dqClazz");
		//转入班级
		//String[] zrClazz = request.getParameterValues("zrClazz");
		//入学时间
		String[] cfrxrq = request.getParameterValues("cfrxrq");
		//当前班级id
		String[] cfcurrentclassid = request.getParameterValues("cfcurrentclassid");
		//当前班级金额
		String[] cfamount = request.getParameterValues("cfamount");
		//实收金额
		String[] cfshje = request.getParameterValues("cfshje");
		//转入班级id
		String[] cfreturninclassid = request.getParameterValues("cfreturninclassid");
		//转入班级金额
		String[] cfzrbjamount = request.getParameterValues("cfzrbjamount");
		//退费金额
		String[] cftfje = request.getParameterValues("cftfje");
		//退费金额
		String[] cfremark = request.getParameterValues("cfremark");
		//添加或修改
		chargeClazzPremiumService.editChargeClazzPremium(chargeClazzPremium,fseq,cfstudentno,cfstudentid,cfidcardno,cfsex,cfmobile,cfrxrq,
				cfcurrentclassid,cfamount,cfshje,cfreturninclassid,cfzrbjamount,cftfje,cfremark,user,time);
		if("".equals(fid) || fid == null){
			//添加日志
            //customLogService.addLog("财务管理", time+"对转班退费单进行添加操作", null, user.getFid());
            List<CustomLog> customLogs = new ArrayList<>();
            for (int i = 0; i < cfstudentid.length; i++) {
                CustomLog customLog = new CustomLog();
                customLog.setFid(GetFid.UUIDIntoFid());
                customLog.setFcreatetime(time);
                customLog.setFcreatorid(user.getFid());
                customLog.setCfstudentid(cfstudentid[i]);
                customLog.setFnameL2("转班退费单");
                customLog.setFdescriptionL2("新增学员:" + cfstudentno[i] + studentName[i] + "单据编号:" + chargeClazzPremium.getFnumber());
                //控制单元
                customLog.setFcontrolunitid(user.getFcompanyid());
                customLogs.add(customLog);
            }
            //添加日志
            customLogService.insertCustomLog(customLogs);
            //重定向到添加页面
			return "redirect:/DrivingSys/insertChargeClazzPremium?type=3";
		}else{
			//添加日志
            //customLogService.addLog("财务管理", time+"对考转班退费单进行修改操作", null, user.getFid());
            List<CustomLog> customLogs = new ArrayList<>();
            for (int i = 0; i < cfstudentid.length; i++) {
                CustomLog customLog = new CustomLog();
                customLog.setFid(GetFid.UUIDIntoFid());
                customLog.setFcreatetime(time);
                customLog.setFcreatorid(user.getFid());
                customLog.setCfstudentid(cfstudentid[i]);
                customLog.setFnameL2("转班退费单");
                customLog.setFdescriptionL2("修改学员:" + cfstudentno[i] + studentName[i] + "单据编号:" + chargeClazzPremium.getFnumber());
                //控制单元
                customLog.setFcontrolunitid(user.getFcompanyid());
                customLogs.add(customLog);
            }
            //添加日志
            customLogService.insertCustomLog(customLogs);
            //重定向到查询页面
			return "redirect:/DrivingSys/queryChargeClazzPremium/look.action?type=4";
		}
	}
	
	/**
	 * 根据fid查询
	 * @param request
	 * @param fid
	 * @return
	 */
	@RequestMapping("/queryChargeClazzPremiumByFid")
	public String queryChargeClazzPremiumByFid(HttpServletRequest request,String fid){
		//查询父类信息
		ChargeClazzPremium chargeClazzPremium = chargeClazzPremiumService.queryChargeClazzPremiumByFid(fid);
		//查询子类信息
		List<ChargeClazzPremiumEntry> chargeClazzPremiumEntrys = chargeClazzPremiumService.selectChargeClazzPremiumEntry(fid, "", "", "");
		//查询班级
		List<Clazz> clazzs = clazzService.selectClazzAll();
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//开始时间
		String endTime = sdf.format(new Date());
		//开结束时间
		String beginTime = TimeFormat.getMonth();
		try {
            Map map = studentController.getPageAsTrainCharge(request, "","","0", "", "","", beginTime, endTime);//调用学员方法
			request.setAttribute("flag", 1);				//是否为新增记录	
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("pb", map.get("pb"));
			request.setAttribute("students", map.get("sList"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
        }
        //查询是否是经办人
        Axamine axamine = new Axamine();
        boolean state = axamine.selectAuditPerson(chargeClazzPremium.getFnumber());
        //把数据传到页面
        request.setAttribute("AuditState", state);
        request.setAttribute("clazzs", clazzs);
		request.setAttribute("chargeClazzPremium", chargeClazzPremium);
		request.setAttribute("chargeClazzPremiumEntrys", chargeClazzPremiumEntrys);
        request.setAttribute("key", ActivitiKey.PUBLIC_KEY);
        return "/backHome/financialManagement/chargeClazzPremiums/editChargeClazzPremiums";
	}
	
	/**
	 * 删除
	 * @param request
	 * @param deleteId 多个
	 * @param fid 单个
	 * @return
	 */
	@RequestMapping("/deleteChargeClazzPremiumByFid")
    public String deleteChargeClazzPremiumByFid(HttpServletRequest request, String[] deleteId, String fid, String[] fnumber, String fnumber2) {
        //获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
        String time = TimeFormat.timeFormat(new Date());
        List<CustomLog> customLogs = new ArrayList<>();
        ActitiviUtil actitiviUtil = new ActitiviUtil();
        List<ChargeClazzPremiumEntry> chargeClazzPremiumEntrys = new ArrayList<>();
        if (fid == null) {
            for (int i = 0; i < deleteId.length; i++) {
                //删除流程
                actitiviUtil.deleteTask(ActivitiKey.PUBLIC_KEY, fnumber[i]);
                chargeClazzPremiumEntrys = chargeClazzPremiumService.selectChargeClazzPremiumEntry(deleteId[i], "", "", "");
                for (ChargeClazzPremiumEntry chargeClazzPremiumEntry : chargeClazzPremiumEntrys) {
                    CustomLog customLog2 = new CustomLog();
                    customLog2.setFid(GetFid.UUIDIntoFid());
                    customLog2.setFcreatetime(time);
                    customLog2.setFcreatorid(user.getFid());
                    customLog2.setCfstudentid(chargeClazzPremiumEntry.getCfstudentid());
                    customLog2.setFnameL2("转班退费单");
                    customLog2.setFdescriptionL2("删除学员:" + chargeClazzPremiumEntry.getCfstudentno() + chargeClazzPremiumEntry.getStudentName());
                    //控制单元
                    customLog2.setFcontrolunitid(user.getFcompanyid());
                    customLogs.add(customLog2);
                }
            }
        } else {
            chargeClazzPremiumEntrys = chargeClazzPremiumService.selectChargeClazzPremiumEntry(fid, "", "", "");
            //删除流程
            actitiviUtil.deleteTask(ActivitiKey.PUBLIC_KEY, fnumber2);
            for (ChargeClazzPremiumEntry chargeClazzPremiumEntry : chargeClazzPremiumEntrys) {
                CustomLog customLog = new CustomLog();
                customLog.setFid(GetFid.UUIDIntoFid());
                customLog.setFcreatetime(time);
                customLog.setFcreatorid(user.getFid());
                customLog.setCfstudentid(chargeClazzPremiumEntry.getCfstudentid());
                customLog.setFnameL2("转班退费单".trim());
                customLog.setFdescriptionL2("删除学员:" + chargeClazzPremiumEntry.getCfstudentno() + chargeClazzPremiumEntry.getStudentName());
                //控制单元
                customLog.setFcontrolunitid(user.getFcompanyid());
                customLogs.add(customLog);
            }
        }
        //删除
		chargeClazzPremiumService.deleteChargeClazzPremiumByFid(deleteId,fid);
		//添加日志
        customLogService.insertCustomLog(customLogs);
        //customLogService.addLog("财务管理", TimeFormat.timeFormat(new Date())+"对考转班退费单进行删除操作", null, user.getFid());
        //重定向到查询页面
		return "redirect:/DrivingSys/queryChargeClazzPremium/look.action";
	}
	
	/**
	 * 审核，反审核
	 * @param request
	 * @param type 1：审核，2：反审核
	 * @param fid
	 * @return
	 */
	@RequestMapping("/auditChargeClazzPremium")
	@ResponseBody
    public int auditChargeClazzPremium(HttpServletRequest request, String type, String fid, String message, String state, String fnumber,
                                       @RequestParam(value = "studentName[]", defaultValue = "") String[] studentName,
                                       @RequestParam(value = "studentId[]", defaultValue = "") String[] studentId,
                                       @RequestParam(value = "cfstudentno[]", defaultValue = "") String[] cfstudentno) {
        //获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//审核，反审核
        int i = chargeClazzPremiumService.auditChargeClazzPremium(fid, type, user.getFid(), message, state, fnumber);
        String ms;
        if ("1".equals(type)) {
            ms = "审核";
        } else {
            ms = "反审核";
        }
        //添加日志
        List<CustomLog> customLogs = new ArrayList<>();
        for (int i1 = 0; i1 < studentId.length; i1++) {
            CustomLog customLog = new CustomLog();
            customLog.setFid(GetFid.UUIDIntoFid());
            customLog.setFcreatetime(TimeFormat.timeFormat(new Date()));
            customLog.setFcreatorid(user.getFid());
            customLog.setCfstudentid(studentId[i1]);
            customLog.setFnameL2("转班退费单");
            customLog.setFdescriptionL2(ms + "学员:" + cfstudentno[i1] + studentName[i1] + "单据编号:" + fnumber);
            //控制单元
            customLog.setFcontrolunitid(user.getFcompanyid());
            customLogs.add(customLog);
        }
        //添加日志
        customLogService.insertCustomLog(customLogs);
        return i;
	}

    /**
     * 提交
     *
     * @param fid
     * @return
     */
    @RequestMapping("/submitChargeClazzPremium")
    @ResponseBody
    public int submitChargeClazzPremium(String fid, HttpServletRequest request, String fnumber, String auditState,
                                        @RequestParam(value = "studentName[]", defaultValue = "") String[] studentName,
                                        @RequestParam(value = "studentId[]", defaultValue = "") String[] studentId,
                                        @RequestParam(value = "cfstudentno[]", defaultValue = "") String[] cfstudentno) {
        ChargeClazzPremium chargeClazzPremium = new ChargeClazzPremium();
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        chargeClazzPremium.setFid(fid);
        chargeClazzPremium.setFlastupdateuserid(user.getFid());
        chargeClazzPremium.setCfbillstate(4L);
        //创建流程
        Axamine axamine = new Axamine();
        axamine.submissionProcess(fnumber);
        int i = chargeClazzPremiumService.modifyChargeClazzPremium(chargeClazzPremium);
        List<CustomLog> customLogs = new ArrayList<>();
        for (int i1 = 0; i1 < studentId.length; i1++) {
            CustomLog customLog = new CustomLog();
            customLog.setFid(GetFid.UUIDIntoFid());
            customLog.setFcreatetime(TimeFormat.timeFormat(new Date()));
            customLog.setFcreatorid(user.getFid());
            customLog.setCfstudentid(studentId[i1]);
            customLog.setFnameL2("转班退费单");
            customLog.setFdescriptionL2("提交学员:" + cfstudentno[i1] + studentName[i1] + "单据编号:" + fnumber);
            //控制单元
            customLog.setFcontrolunitid(user.getFcompanyid());
            customLogs.add(customLog);
        }
        //添加日志
        customLogService.insertCustomLog(customLogs);
        return i;
    }

	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<ChargeClazzPremium> getPageBean2(HttpServletRequest request,String companyId,String studentName,String cfidcardno,String cfmobile,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		//拼接SQL语句
		StringBuffer psql = new  StringBuffer();
		psql.append("select distinct t.fid,t.fnumber,t.fdescription,to_char(t.fbizdate,'yyyy-MM-dd')fbizdate,u.name createName,us.name auditName,t.cfbillstate,t.CFDESC ");
		psql.append("from ct_ds_transferclazzrefundbill t left join t_e_user u  on t.fcreatorid=u.fid left join t_e_user us on t.fauditorid = us.fid ");
		psql.append("left join CT_DS_TRANSFERCRBE tr on tr.fparentid = t.fid left join ct_ds_student s on tr.cfstudentid = s.fid where t.fcontrolunitid = '"+companyId+"'");
		if("".equals(studentName)){
			
		}else{
			psql.append(" and s.fname_l2 like '%"+studentName+"%'");
		}
		if("".equals(cfidcardno)){
			
		}else{
			psql.append(" and tr.cfidcardno like '%"+cfidcardno+"%'");
		}
		if("".equals(cfmobile)){
			
		}else{
			psql.append(" and tr.cfmobile = '"+cfmobile+"'");
		}
		if("".equals(beginTime) || "".equals(endTime)){

		}else{
			psql.append("  and t.fbizdate between to_date('"+beginTime+"','YYYY/MM/DD') and to_date('"+endTime+"','YYYY/MM/DD')");
		}
        psql.append(" order by to_char(t.fbizdate,'yyyy-MM-dd') desc");
        int psize = 100; //每页显示数
        int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= chargeClazzPremiumService.selectPage(pindex, psql.toString(), psize);
		PageBean<ChargeClazzPremium> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<ChargeClazzPremium>)paramMap.get("v_cur")); //每页数据集
		return pb;
	}
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<ChargeClazzPremium> getPageBean(HttpServletRequest request,String companyId,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		//拼接SQL语句
		StringBuffer psql = new  StringBuffer();
		psql.append("select t.fid,t.fnumber,t.fdescription,to_char(t.fbizdate,'yyyy-MM-dd')fbizdate,u.name createName,us.name auditName,t.cfbillstate,t.CFDESC from ");
		psql.append(" ct_ds_transferclazzrefundbill t left join t_e_user u  on t.fcreatorid=u.fid left join t_e_user us on t.fauditorid = us.fid where t.fcontrolunitid = '"+companyId+"'");
		psql.append(" and t.fbizdate between to_date('"+beginTime+"','YYYY/MM/DD') and to_date('"+endTime+"','YYYY/MM/DD')");
        psql.append(" order by to_char(t.fbizdate,'yyyy-MM-dd') desc");
        int psize = 10; //每页显示数
        int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= chargeClazzPremiumService.selectPage(pindex, psql.toString(), psize);
		PageBean<ChargeClazzPremium> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<ChargeClazzPremium>)paramMap.get("v_cur")); //每页数据集
		return pb;
	}

	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=chargeClazzPremiumService.selectPageCount(psql);
		return paramMap;		
	}

    /**
     * 导出Execl
     *
     * @param response
     * @param request
     * @param studentName
     * @param cfidcardno
     * @param cfmobile
     * @param beginTime
     * @param endTime
     */
    @RequestMapping("/importChargeClazzPremium")
    public void importChargeClazzPremium(HttpServletResponse response, HttpServletRequest request, String studentName, String cfidcardno, String cfmobile, String beginTime, String endTime) {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        //定义接收类型
        List<Object> data = new ArrayList<>();
        //查询数据
        List<ChargeClazzPremiumEntry> chargeClazzPremiumEntrys = chargeClazzPremiumService.selectChargeClazzPremiumByInfo(user.getFcompanyid(), studentName.trim(), cfidcardno.trim(), cfmobile.trim(), beginTime, endTime);
        for (ChargeClazzPremiumEntry chargeClazzPremiumEntry : chargeClazzPremiumEntrys) {
            data.add(chargeClazzPremiumEntry.getFnumber());
            data.add(chargeClazzPremiumEntry.getFbizdate());
            data.add(chargeClazzPremiumEntry.getFdescription());
            data.add(chargeClazzPremiumEntry.getShName());
            data.add(chargeClazzPremiumEntry.getZdrName());
            data.add(stateToString.billsState(chargeClazzPremiumEntry.getCfbillstate()));
            data.add(chargeClazzPremiumEntry.getStudentName());
            data.add(chargeClazzPremiumEntry.getCfstudentno());
            data.add(chargeClazzPremiumEntry.getCfidcardno());
            data.add(chargeClazzPremiumEntry.getCfmobile());
            data.add(chargeClazzPremiumEntry.getCfrxsj2());
            data.add(chargeClazzPremiumEntry.getCfshje());
            data.add(chargeClazzPremiumEntry.getCftfje());
            data.add(chargeClazzPremiumEntry.getCfremark());
            data.add(chargeClazzPremiumEntry.getCfdesc());
            data.add(stateToString.sex(chargeClazzPremiumEntry.getCfsex()));
            data.add(chargeClazzPremiumEntry.getDqClazz());
            data.add(chargeClazzPremiumEntry.getCfamount());
            data.add(chargeClazzPremiumEntry.getZrClazz());
            data.add(chargeClazzPremiumEntry.getCfzrbjamount());
        }
        //头部数据
        String head[] = {"单据编码", "业务日期", "参考信息", "审核人名称", "制单人名称", "单据状态", "学员", "学员编码", "身份证号", "手机", "入学时间", "实收金额", "退费金额", "备注", "摘要", "性别", "当前班级", "当前班级金额", "转入班级", "转入班级金额"};
        //长度
        Integer length[] = {7300, 3000, 4000, 3000, 3000, 2600, 3000, 5000, 5000, 3000, 3500, 2600, 2600, 5000, 5000, 2600, 4500, 2600, 4500, 2600};

        List<Object> data2=listForSum(data,head,11,2,4);
        try {
            //导出Excel
            CommonExport.importExcelSum(response, "转班退费单" + TimeFormat.getNowTime2() + ".xls", "转班退费单",data,head, length,data2);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
	/**
	 *统计方法
	 * @param list1 完成遍历的集合
	 * @param head  表头
	 * @param num   求和的第一个字段的下标
	 * @param sum  需要增加多少个字段
	 * @return
	 */
	public List<Object> listForSum(List<Object> list1,String[] head,int num,int sum,int nextNum){

		Double money=0.0;

		List<Object>list=new ArrayList<>();
		DecimalFormat df = new DecimalFormat("#.00");
		list.add("合计：");
		for (int i = 0; i < num - 1; i++) {
			list.add(" ");
		}
		for(int i=0;i<sum;i++){

			for (int j = 0; j < list1.size() / head.length; j++) {
				if (list1.get(num + head.length*j)==null || "".equals(list1.get(num + head.length * j))) {
					money += 0.00;
				} else {
					money += Double.parseDouble(list1.get(num + head.length * j) + "");
				}
			}
			if(money==0.00||money==null){
				list.add("0.00");
			}else{
				if(".00".equals(df.format(money))){
					list.add("");
				}else{
					list.add(df.format(money));
				}
			}
			num++;
			money = 0.00;
		}
		for(int i=0;i<nextNum;i++){
			list.add("");
		}
		num+=nextNum;
		for (int j = 0; j < list1.size() / head.length; j++) {
			if (list1.get(num + head.length*j)==null || "".equals(list1.get(num + head.length * j))) {
				money += 0.00;
			} else {
				money += Double.parseDouble(list1.get(num + head.length * j) + "");
			}
		}
		if(money==0.00||money==null){
			list.add("0.00");
		}else{
			if(".00".equals(df.format(money))){
				list.add("");
			}else{
				list.add(df.format(money));
			}
		}
		num++;
		money = 0.00;
		list.add("");
		num+=1;
		for (int j = 0; j < list1.size() / head.length; j++) {
			if (list1.get(num + head.length*j)==null || "".equals(list1.get(num + head.length * j))) {
				money += 0.00;
			} else {
				money += Double.parseDouble(list1.get(num + head.length * j) + "");
			}
		}
		if(money==0.00||money==null){
			list.add("0.00");
		}else{
			if(".00".equals(df.format(money))){
				list.add("");
			}else{
				list.add(df.format(money));
			}
		}
		return list;
	}
}
